package com.sb.framework.util;

import java.io.OutputStream;
import java.util.List;

import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.VerticalAlignment;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

/**
 * excel操作类
 * 
 * @author LIUTQ
 *
 */
public class ExcelUtil {

	/**
	 * 创建excel文档并写入outStream输出流中
	 * 
	 * @param outStream
	 * @param mainTitle
	 * @param titles
	 * @param contents
	 */
	public final static void buildExcel(OutputStream outStream, String mainTitle, String[] titles, List<String[]> contents) {
		int beginRow = 0;
		try {
			/** **********创建工作簿************ */
			WritableWorkbook workbook = Workbook.createWorkbook( outStream );

			/** **********创建工作表************ */
			WritableSheet sheet = workbook.createSheet( "Sheet1", 0 );

			/** **********设置纵横打印（默认为纵打）、打印纸***************** */
			jxl.SheetSettings sheetset = sheet.getSettings();
			sheetset.setProtected( false );

			/** ************设置单元格字体************** */
			WritableFont NormalFont = new WritableFont( WritableFont.ARIAL, 10 );
			WritableFont BoldFont = new WritableFont( WritableFont.ARIAL, 10, WritableFont.BOLD );

			/** ************以下设置三种单元格样式************ */
			// 用于标题居中
			WritableCellFormat wcf_center = new WritableCellFormat( BoldFont );
			wcf_center.setBorder( Border.ALL, BorderLineStyle.THIN ); // 线条
			wcf_center.setVerticalAlignment( VerticalAlignment.CENTRE ); // 文字垂直对齐
			wcf_center.setAlignment( Alignment.CENTRE ); // 文字水平对齐
			wcf_center.setWrap( false ); // 文字是否换行

			// 用于正文居左
			WritableCellFormat wcf_left = new WritableCellFormat( NormalFont );
			wcf_left.setBorder( Border.ALL, BorderLineStyle.THIN ); // 线条
			wcf_left.setVerticalAlignment( VerticalAlignment.CENTRE ); // 文字垂直对齐
			wcf_left.setAlignment( Alignment.CENTRE ); // 文字水平对齐
			wcf_left.setWrap( false ); // 文字是否换行

			// excel大标题
			if (mainTitle != null && !mainTitle.equals( "" )) {
				sheet.mergeCells( 0, 0, titles.length - 1, 0 );
				sheet.addCell( new Label( 0, 0, mainTitle, wcf_center ) );
				beginRow = beginRow + 1;
			}

			/** ***************以下是EXCEL第一行列标题********************* */
			if (titles != null && titles.length > 1) {
				for (int i = 0; i < titles.length; i++) {
					sheet.addCell( new Label( i, beginRow, titles[ i ], wcf_center ) );
				}
				beginRow = beginRow + 1;
			}
			/** ***************以下是EXCEL正文数据********************* */
			for (int i = 0; i < contents.size(); i++) {// row
				String[] rowContent = contents.get( i );
				for (int j = 0; j < titles.length; j++) { // cell
					String content = "";
					if (j < rowContent.length) {
						content = rowContent[ j ];
					}
					sheet.addCell( new Label( j, i + beginRow, content, wcf_left ) );
				}
			}
			workbook.write();
			workbook.close();

		} catch (Exception e) {

		}
	}

}
